package com.leave.dao;

import com.leave.entity.Student;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLOutput;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 访问用户数据DAO类，封装访问数据表的常用CRUD方法
 *
 */
public class StudentDao {
    /**
     *  注意:
     *  (1)成员变量的数据类型，如果是基本数据类型，在setter注入的xml配置，
     *  使用value，如果是引用型，就用ref
     *  <property name="声明成员变量" [value=""| ref="IoC容器中配置bean的id"]
     *  (2)使用jdbcTemplate框架访问数据库，在配置声明式事务管理后，
     *      就不需要开发者手动关闭数据库连接资源，由Spring自动管理。
     */
    private JdbcTemplate jdbcTemplate;

    /**
     * 插入
     * @param entity
     */
    public void insert(Student entity) {
        //声明插入SQL字符串变量sql
        String sql = "insert into sys_student(stuID,classID,stuName,sex,address,stuTel,contact,contactTel)values(?,?,?,?,?,?,?,?)";
        if (entity != null) {
            String[] paramValues = {entity.getStuID(), entity.getClassID(),entity.getStuName(),
                    entity.getSex(),entity.getAddress(),entity.getStuTel(),entity.getContact(),entity.getContactTel()};
            jdbcTemplate.update(sql, paramValues);
        }
    }

    /**
     * 删除
     * @param stuID
     */
    public void delete(String stuID){
        String sql ="delete from sys_student where  stuID=?";
        String[]paramValues={ stuID};
        jdbcTemplate.update(sql,paramValues);
    }

    /**
     * 更新
     * @param entity
     */
    public void update(Student entity){
            //声明插入SQL字符串变量sql
            String sql = "update sys_student set stuID=?,classID=?,stuName=?,sex=?,address=?,stuTel=?,contact=?,contactTel=? where stuID=?";
            if (entity != null) {
                String[] paramValues = {entity.getStuID(), entity.getClassID(),entity.getStuName(),
                        entity.getSex(),entity.getAddress(),entity.getStuTel(),entity.getContact(),entity.getContactTel()};
                jdbcTemplate.update(sql, paramValues);
            }
        }

    /**
     *
     * @param entity
     * @return
     * student 学生
     */
    public List<Student> query(Student entity){
        String sql ="select * from sys_student where 1=1 ";
        List<Object> paramValues = new ArrayList<>();
        List<Student> userList = new ArrayList<>();
//        String password1= entity.getPassword();
//        System.out.println(password1);
        //动态查询
        if(entity!=null){
            if(entity.getStuID() != null&&!"".equals(StringUtils.trim(entity.getStuID()))){
                 sql += "and stuID=?";
                paramValues.add(entity.getStuID());
            }
            if(entity.getPassword() != null&&!"".equals(StringUtils.trim(entity.getPassword()))){
                sql += "and password=?";
                paramValues.add(entity.getPassword());
            }

        }
//        System.out.println(sql);
        userList =jdbcTemplate.query(sql, paramValues.toArray(), new RowMapper<Student>() {
            @Override
            public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
                Student user = new Student();
                String stuID =rs.getString("stuID");
                String classID =rs.getString("classID");
                String stuName =rs.getString("stuName");
                String sex =rs.getString("sex");
                String address =rs.getString("address");
                String stuTel =rs.getString("stuTel");
                String contact =rs.getString("contact");
                String contactTel =rs.getString("contactTel");
                String password =rs.getString("password");
                user.setStuID(stuID);
                user.setClassID(classID);
                user.setStuName(stuName);
                user.setSex(sex);
                user.setAddress(address);
                user.setStuTel(stuTel);
                user.setContact(contact);
                user.setContactTel(contactTel);
                user.setPassword(password);
//                System.out.println("name"+password);
                return user;
            }
        });
//        System.out.println("111"+userList);
        return  userList;
    }

    public List<Map<String, Object>> selectStuList(String keyword) throws SQLException {
        String sql = "SELECT * FROM sys_student WHERE stuID LIKE ? OR classID LIKE ? OR stuName LIKE ? OR sex LIKE ? OR address LIKE ? OR stuTel LIKE ? OR contact LIKE ? OR contactTel LIKE ?";
        String likeKeyword = "%" + keyword + "%";
        String[] paramValues = {likeKeyword, likeKeyword, likeKeyword, likeKeyword, likeKeyword, likeKeyword, likeKeyword, likeKeyword};
        RowMapper<Map<String, Object>> rowMapper = new RowMapper<Map<String, Object>>() {
            @Override
            public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                Map<String, Object> map = new HashMap<>();
                int columnCount = rs.getMetaData().getColumnCount();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = rs.getMetaData().getColumnName(i);
                    Object columnValue = rs.getObject(i);
                    map.put(columnName, columnValue);
                }
                return map;
            }
        };
        return jdbcTemplate.query(sql, paramValues, rowMapper);
    }

    /**
     *  setter注入方式，注意不需要定义getter方法。
     * @param jdbcTemplate
     */
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
}
